********************************************************************************
* Summary variables
********************************************************************************

include choose_version.do

* Transfer
if `version_robust' == 2 {
	egen transfer = rowtotal(snap_impute_val_hh housing_assist_impute_val_hh incwelfr_hh credits_hh state_credits_hh incunemp_hh), missing
}
else if `version_robust' == 6 {
	egen transfer = rowtotal(snap_impute_val_hh housing_assist_impute_val_hh incwelfr_hh credits_hh state_credits_hh mcaid_impute_val_hh ssi_impute_val_hh), missing
}
else {
	egen transfer = rowtotal(snap_impute_val_hh housing_assist_impute_val_hh incwelfr_hh credits_hh state_credits_hh), missing
}

* Tax
if `version_robust' == 4 {
	egen tax = rowtotal(fedtax_hh ctccrd_hh fica_hh fedretir_hh fica_employer_hh statetax_hh divtax_hh_neg), missing
}
else if `version_robust' == 5 {
	egen tax = rowtotal(fedtax_hh fica_hh fedretir_hh fica_employer_hh stataxac_pos_hh divtax_hh_neg), missing
}
else if `version_robust' == 8 {
	egen tax = rowtotal(fedtaxac_pos_hh fica_hh fedretir_hh stataxac_pos_hh divtax_hh_neg), missing
}
else if `version_robust' == 9 {
	egen tax = rowtotal(fedtaxac_pos_hh fica_hh fedretir_hh fica_employer_hh stataxac_pos_hh), missing
}
else {
	egen tax = rowtotal(fedtaxac_pos_hh fica_hh fedretir_hh fica_employer_hh stataxac_pos_hh divtax_hh_neg), missing
}

* Tax-and-transfer
gen tax_transfer = tax - transfer

********************************************************************************
* Mean rates of summary variables
********************************************************************************

putexcel set $figure_path/cps_results_`version_robust'.xlsx, sheet(t&T_rates_mean) modify open
putexcel A1 = "Program"
putexcel B1 = "Q1"
putexcel C1 = "Q2"
putexcel D1 = "Q3"
putexcel E1 = "Q4"
putexcel F1 = "Q5"

* Tax-transfer rate
gen tax_transfer_rate = tax_transfer/inc_tot_hh

putexcel A2 = "t&T"
sum tax_transfer_rate if inc_tot_hh_quintiles == 1 [fw = asecwth]
putexcel B2 = `r(mean)'
sum tax_transfer_rate if inc_tot_hh_quintiles == 2 [fw = asecwth]
putexcel C2 = `r(mean)'
sum tax_transfer_rate if inc_tot_hh_quintiles == 3 [fw = asecwth]
putexcel D2 = `r(mean)'
sum tax_transfer_rate if inc_tot_hh_quintiles == 4 [fw = asecwth]
putexcel E2 = `r(mean)'
sum tax_transfer_rate if inc_tot_hh_quintiles == 5 [fw = asecwth]
putexcel F2 = `r(mean)'

* Transfer rate
gen transfer_rate = transfer/inc_tot_hh

putexcel A3 = "Transfer"
sum transfer_rate if inc_tot_hh_quintiles == 1 [fw = asecwth]
putexcel B3 = `r(mean)'
sum transfer_rate if inc_tot_hh_quintiles == 2 [fw = asecwth]
putexcel C3 = `r(mean)'
sum transfer_rate if inc_tot_hh_quintiles == 3 [fw = asecwth]
putexcel D3 = `r(mean)'
sum transfer_rate if inc_tot_hh_quintiles == 4 [fw = asecwth]
putexcel E3 = `r(mean)'
sum transfer_rate if inc_tot_hh_quintiles == 5 [fw = asecwth]
putexcel F3 = `r(mean)'

* Tax rate
gen tax_rate = tax/inc_tot_hh

putexcel A4 = "Tax"
sum tax_rate if inc_tot_hh_quintiles == 1 [fw = asecwth]
putexcel B4 = `r(mean)'
sum tax_rate if inc_tot_hh_quintiles == 2 [fw = asecwth]
putexcel C4 = `r(mean)'
sum tax_rate if inc_tot_hh_quintiles == 3 [fw = asecwth]
putexcel D4 = `r(mean)'
sum tax_rate if inc_tot_hh_quintiles == 4 [fw = asecwth]
putexcel E4 = `r(mean)'
sum tax_rate if inc_tot_hh_quintiles == 5 [fw = asecwth]
putexcel F4 = `r(mean)'

bysort inc_tot_hh_quintiles: sum tax_transfer_rate transfer_rate tax_rate [fw = asecwth]

putexcel save

********************************************************************************
* Total summary variables divided by total income for quintiles
********************************************************************************

* Total income
sum inc_tot_hh if inc_tot_hh_quintiles == 1 [fw = asecwth]
scalar inc_tot_q1 = r(sum)
sum inc_tot_hh if inc_tot_hh_quintiles == 2 [fw = asecwth]
scalar inc_tot_q2 = r(sum)
sum inc_tot_hh if inc_tot_hh_quintiles == 3 [fw = asecwth]
scalar inc_tot_q3 = r(sum)
sum inc_tot_hh if inc_tot_hh_quintiles == 4 [fw = asecwth]
scalar inc_tot_q4 = r(sum)
sum inc_tot_hh if inc_tot_hh_quintiles == 5 [fw = asecwth]
scalar inc_tot_q5 = r(sum)

putexcel set $figure_path/cps_results_`version_robust'.xlsx, sheet(t&T_rates_cbo) modify open
putexcel A1 = "Program"
putexcel B1 = "Q1"
putexcel C1 = "Q2"
putexcel D1 = "Q3"
putexcel E1 = "Q4"
putexcel F1 = "Q5"

* Tax-transfer
sum tax_transfer if inc_tot_hh_quintiles == 1 [fw = asecwth]
scalar tax_transfer_q1 = r(sum)
sum tax_transfer if inc_tot_hh_quintiles == 2 [fw = asecwth]
scalar tax_transfer_q2 = r(sum)
sum tax_transfer if inc_tot_hh_quintiles == 3 [fw = asecwth]
scalar tax_transfer_q3 = r(sum)
sum tax_transfer if inc_tot_hh_quintiles == 4 [fw = asecwth]
scalar tax_transfer_q4 = r(sum)
sum tax_transfer if inc_tot_hh_quintiles == 5 [fw = asecwth]
scalar tax_transfer_q5 = r(sum)

scalar tax_transfer_rate_q1 = tax_transfer_q1/inc_tot_q1
scalar tax_transfer_rate_q2 = tax_transfer_q2/inc_tot_q2
scalar tax_transfer_rate_q3 = tax_transfer_q3/inc_tot_q3
scalar tax_transfer_rate_q4 = tax_transfer_q4/inc_tot_q4
scalar tax_transfer_rate_q5 = tax_transfer_q5/inc_tot_q5

scalar list tax_transfer_rate_q1 tax_transfer_rate_q2 tax_transfer_rate_q3 tax_transfer_rate_q4 tax_transfer_rate_q5
putexcel A2 = "t&T"
putexcel B2 = tax_transfer_rate_q1
putexcel C2 = tax_transfer_rate_q2
putexcel D2 = tax_transfer_rate_q3
putexcel E2 = tax_transfer_rate_q4
putexcel F2 = tax_transfer_rate_q5

* Transfer
sum transfer if inc_tot_hh_quintiles == 1 [fw = asecwth]
scalar transfer_q1 = r(sum)
sum transfer if inc_tot_hh_quintiles == 2 [fw = asecwth]
scalar transfer_q2 = r(sum)
sum transfer if inc_tot_hh_quintiles == 3 [fw = asecwth]
scalar transfer_q3 = r(sum)
sum transfer if inc_tot_hh_quintiles == 4 [fw = asecwth]
scalar transfer_q4 = r(sum)
sum transfer if inc_tot_hh_quintiles == 5 [fw = asecwth]
scalar transfer_q5 = r(sum)

scalar transfer_rate_q1 = transfer_q1/inc_tot_q1
scalar transfer_rate_q2 = transfer_q2/inc_tot_q2
scalar transfer_rate_q3 = transfer_q3/inc_tot_q3
scalar transfer_rate_q4 = transfer_q4/inc_tot_q4
scalar transfer_rate_q5 = transfer_q5/inc_tot_q5

scalar list transfer_rate_q1 transfer_rate_q2 transfer_rate_q3 transfer_rate_q4 transfer_rate_q5
putexcel A3 = "Transfer"
putexcel B3 = transfer_rate_q1
putexcel C3 = transfer_rate_q2
putexcel D3 = transfer_rate_q3
putexcel E3 = transfer_rate_q4
putexcel F3 = transfer_rate_q5

* Tax
sum tax if inc_tot_hh_quintiles == 1 [fw = asecwth]
scalar tax_q1 = r(sum)
sum tax if inc_tot_hh_quintiles == 2 [fw = asecwth]
scalar tax_q2 = r(sum)
sum tax if inc_tot_hh_quintiles == 3 [fw = asecwth]
scalar tax_q3 = r(sum)
sum tax if inc_tot_hh_quintiles == 4 [fw = asecwth]
scalar tax_q4 = r(sum)
sum tax if inc_tot_hh_quintiles == 5 [fw = asecwth]
scalar tax_q5 = r(sum)

scalar tax_rate_q1 = tax_q1/inc_tot_q1
scalar tax_rate_q2 = tax_q2/inc_tot_q2
scalar tax_rate_q3 = tax_q3/inc_tot_q3
scalar tax_rate_q4 = tax_q4/inc_tot_q4
scalar tax_rate_q5 = tax_q5/inc_tot_q5

scalar list tax_rate_q1 tax_rate_q2 tax_rate_q3 tax_rate_q4 tax_rate_q5
putexcel A4 = "Tax"
putexcel B4 = tax_rate_q1
putexcel C4 = tax_rate_q2
putexcel D4 = tax_rate_q3
putexcel E4 = tax_rate_q4
putexcel F4 = tax_rate_q5

putexcel save

scalar drop _all
